package cn.analysis.project.mapper;

import cn.analysis.project.domain.SysLevel;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/** 分层健康度mapper */
@Repository
public interface SysLevelMapper extends BaseMapper<SysLevel> {

    /** 获取实时分层健康度 */
    @Select(value = "SELECT l.diagtime, l.health_rating*100 health, ml.name FROM `monitoringlevel` ml, level l " +
            "where DATE_FORMAT(diagtime, '%Y-%m-%d') = DATE_FORMAT(CURDATE(), '%Y-%m-%d') " +
            "and ml.ID = l.L_ID and l.diagtime=(select max(diagtime) from level where ml.ID=L_ID) " +
            "and l.code=#{code} order by diagtime asc")
    List<Map<String, Object>> selectListNow(String code);

    /** 通过分层名获取历史分层健康度 日 */
    @Select(value = "SELECT ml.name, l.diagtime, l.health_rating*100 health from monitoringlevel ml, level l " +
            "where ml.ID = l.L_ID and DATE_FORMAT(DIAGTIME, '%Y-%m-%d') = DATE_FORMAT(now(), '%Y-%m-%d') " +
            "and ml.name=#{name} and l.code=#{code} order by l.diagtime asc")
    List<Map<String, Object>> selectListDayByName(String name, String code);

    /** 周 */
    @Select(value = "SELECT (DAY(l.diagtime)+WEEKDAY(l.diagtime-INTERVAL DAY(l.diagtime) DAY)) DIV 7 + 1 weeks, ml.name, avg(l.health_rating)*100 health " +
            "from monitoringlevel ml, level l where ml.ID = l.L_ID and DATE_FORMAT(l.DIAGTIME, '%Y-%m') = DATE_FORMAT(now(), '%Y-%m') " +
            "and ml.name=#{name} and l.code=#{code} group by weeks order by l.diagtime asc")
    List<Map<String, Object>> selectListWeekByName(String name, String code);

    /** 按月、名称统计历史分层健康度 */
    @Select(value = "SELECT ml.name, avg(l.health_rating)*100 health, DATE_FORMAT(diagtime,'%Y-%m') month from monitoringlevel ml, level l " +
            "where ml.ID = l.L_ID and DATE_FORMAT(DIAGTIME, '%Y') = DATE_FORMAT(now(), '%Y') " +
            "and ml.name=#{name} and l.code=#{code} group by month order by month asc")
    List<Map<String, Object>> selectListMonthByName(String name, String code);

    /** 按季、名称统计历史分层健康度 */
    @Select(value = "SELECT ml.name, avg(l.health_rating)*100 health, (select QUARTER(l.diagtime)) quarters from monitoringlevel ml, level l " +
            "where ml.ID = l.L_ID and DATE_FORMAT(DIAGTIME, '%Y') = DATE_FORMAT(now(), '%Y') " +
            "and ml.name=#{name} and l.code=#{code} group by quarters order by quarters asc")
    List<Map<String, Object>> selectListQuarterByName(String name, String code);

    /** 按年、名称统计历史分层健康度 */
    @Select(value = "SELECT ml.name, avg(l.health_rating)*100 health, DATE_FORMAT(l.diagtime,'%Y') year from monitoringlevel ml, level l " +
            "where ml.ID = l.L_ID and ml.name=#{name} and l.code=#{code} group by year order by year asc")
    List<Map<String, Object>> selectListYearByName(String name, String code);
}
